package org.acooly.secretbox.service.impl;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.acooly.android.utils.DateUtils;
import org.acooly.android.utils.Page;
import org.acooly.android.utils.StringUtils;
import org.acooly.secretbox.service.AccountService;
import org.acooly.secretbox.service.domain.Account;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AccountServiceImpl extends SQLiteOpenHelper implements AccountService {

	private static final String DATABASE_NAME = "account.db";
	private static final String TABLE_NAME = "ACCOUNT";
	private static final String TABLE_CREATE = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_NAME
			+ "(ID INTEGER PRIMARY KEY AUTOINCREMENT, accountName text not null,username text not null,PASSWORD text not null,CREATETIME date not null, accountType text not null,strength int not null,comments text );";

	public AccountServiceImpl(Context context) {
		super(context, DATABASE_NAME, null, 1);
	}

	@Override
	public void create(Account account) {
		save(account);
	}

	@Override
	public void update(Account account) {
		save(account);
	}

	public void save(Account account) {
		SQLiteDatabase db = null;
		try {

			ContentValues values = new ContentValues();
			values.put("ACCOUNTNAME", account.getAccountName());
			values.put("USERNAME", account.getUsername());
			values.put("PASSWORD", account.getPassword());
			values.put("CREATETIME", DateUtils.formart(account.getCreateTime()));
			values.put("ACCOUNTTYPE", account.getAccountType());
			values.put("strength", account.getStrength());
			values.put("comments", account.getComments());
			db = this.getWritableDatabase();
			if (account.getId() == 0) {
				// create
				db.insert(TABLE_NAME, null, values);
			} else {
				// update
				db.update(TABLE_NAME, values, "id=?", new String[] { String.valueOf(account.getId()) });
			}

		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			throw new RuntimeException("保存账户失败:" + e.getMessage());
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
	}

	@Override
	public void delete(int id) {
		SQLiteDatabase db = null;
		try {
			db = getWritableDatabase();
			db.delete(TABLE_NAME, " id = " + id, null);
		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			throw new RuntimeException("删除账户失败");
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
	}

	@Override
	public Account get(int id) {
		SQLiteDatabase db = getReadableDatabase();
		try {
			Cursor cur = db.rawQuery("select * from ACCOUNT where id = ?", new String[] { String.valueOf(id) });
			Account account = null;
			if (cur.moveToNext()) {
				account = new Account();
				account.setId(cur.getInt(0));
				account.setAccountName(cur.getString(1));
				account.setUsername(cur.getString(2));
				account.setPassword(cur.getString(3));
				account.setCreateTime(DateUtils.parse(cur.getString(4)));
				account.setAccountType(cur.getString(5));
				account.setStrength(cur.getInt(6));
				account.setComments(cur.getString(7));
				Log.i("Get Account", account.toString());
			}
			cur.close();
			return account;
		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			throw new RuntimeException("获取账户不存在:" + e.getMessage());
		} finally {
			db.close();
		}
	}

	public int getCount(String sql) {
		SQLiteDatabase db = null;
		try {
			db = this.getReadableDatabase();
			Cursor mCount = db.rawQuery(sql, null);
			mCount.moveToFirst();
			int count = mCount.getInt(0);
			mCount.close();
			return count;
		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			return 0;
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
	}

	@Override
	public int getCount() {
		SQLiteDatabase db = null;
		try {
			db = this.getReadableDatabase();
			Cursor mCount = db.rawQuery("select count(*) from ACCOUNT", null);
			mCount.moveToFirst();
			int count = mCount.getInt(0);
			mCount.close();
			return count;
		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			return 0;
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
	}

	@Override
	public Page<Account> retrieve(int startRow, int pageSize, Map<String, String> conditions) {

		SQLiteDatabase db = null;
		try {
			String sql = "select * from ACCOUNT";
			String sqlCount = "select count(*) from ACCOUNT";
			String conditionSql = " where 1 = 1";
			if (conditions != null) {
				String accountName = conditions.get("accountName");
				if (StringUtils.isNotBlank(accountName)) {
					conditionSql += " and accountName like '%" + accountName + "%'";
				}
				String accountType = conditions.get("accountType");
				if (StringUtils.isNotBlank(accountType) && !StringUtils.equals(accountType, "全部")) {
					conditionSql += " and accountType ='" + accountType + "'";
				}
				String strength = conditions.get("strength");
				if (StringUtils.isNotBlank(strength)) {
					conditionSql += " and strength =" + strength;
				}
			}
			sql += conditionSql + " order by id desc limit ?,?";
			sqlCount += conditionSql;
			int total = getCount(sqlCount);
			db = getReadableDatabase();
			Cursor cur = db.rawQuery(sql, new String[] { String.valueOf(startRow), String.valueOf(pageSize) });
			List<Account> accounts = new LinkedList<Account>();
			while (cur.moveToNext()) {
				Account account = new Account();
				account.setId(cur.getInt(0));
				account.setAccountName(cur.getString(1));
				account.setUsername(cur.getString(2));
				account.setPassword(cur.getString(3));
				account.setCreateTime(DateUtils.parse(cur.getString(4)));
				account.setAccountType(cur.getString(5));
				account.setStrength(cur.getInt(6));
				account.setComments(cur.getString(7));
				accounts.add(account);
			}
			cur.close();
			Page<Account> page = new Page<Account>(startRow, pageSize);
			page.setTotal(total);
			page.setData(accounts);
			return page;
		} catch (Exception e) {
			Log.e("AccountService", e.getMessage());
			throw new RuntimeException("查询账户失败:" + e.getMessage());
		} finally {
			db.close();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(DATABASE_NAME, TABLE_CREATE);
		db.execSQL(TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}
